package dao;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import utilClasses.ListaLinija;
import utilClasses.uLinija;

public class daoLinija {
	public static String unesiLiniju(String linija, double cijena,int idMape,String prevoznik,String saobracaj) throws SQLException{
		String rez=new String("true");
		Connection c = daoKonekcija.dajKonekciju();
		try {
			CallableStatement cStmt1 = c.prepareCall("{CALL dajIdPrevoznika(?)}"); 
			cStmt1.setString(1,prevoznik);
			ResultSet rs1 = cStmt1.executeQuery(); ;
			int idp=0;
			if(rs1.next()) {
				idp=rs1.getInt(1);
			}
			

CallableStatement cStmt2 = c.prepareCall("{CALL dajIdTipaSaobracaja (?)}"); 
			cStmt2.setString(1,saobracaj);
			ResultSet rs2 = cStmt2.executeQuery();
			int ids=0;
			if(rs2.next()) {
				ids=rs2.getInt(1);
			}
			
			
			CallableStatement cStmt3 = c.prepareCall("{CALL Insert_Linija(?,?,?,?,?)}");
			cStmt3.setInt(1, ids);
			cStmt3.setString(2, linija);
			cStmt3.setInt(3, idMape);
			cStmt3.setDouble(4, cijena);
			cStmt3.setInt(5, idp);
			cStmt3.execute();
			cStmt3.close();
			c.close();
			return rez;
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			if(c!=null)c.close();
			e.printStackTrace();
			return new String("false");
		}
	}
	
	
	public static String dajImePrevoznika(int prevoznik) throws SQLException{
		Connection c = daoKonekcija.dajKonekciju();
		CallableStatement cStmt3;
		String imePrevoznika=null;
		try {
			cStmt3 = c.prepareCall("{CALL dajPrevoznika(?)}");
		
		
		cStmt3.setInt(1, prevoznik);
    	ResultSet rs1 = cStmt3.executeQuery(); ;
		
		if(rs1.next()) {
			imePrevoznika=rs1.getString(1);
		}
		rs1.close();
		cStmt3.close();
		c.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			if(c!=null)c.close();
			e.printStackTrace();
		}
		return imePrevoznika;
	}
	
	public static String dajTipSaobracaja(int tip) throws SQLException{
		Connection c = daoKonekcija.dajKonekciju();
		CallableStatement cStmt3;
		String ime=null;
		try {
			cStmt3 = c.prepareCall("{CALL dajTipSaobracaja (?)}");
		
		
		cStmt3.setInt(1, tip);
    	ResultSet rs1 = cStmt3.executeQuery(); ;
		
		if(rs1.next()) {
			ime=rs1.getString(1);
		}
		rs1.close();
		cStmt3.close();
		c.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			if(c!=null)c.close();
			e.printStackTrace();
		}
		return ime;
	}
	public static ListaLinija dajLinije(String id) throws SQLException
	{
		Connection c = daoKonekcija.dajKonekciju();
		ListaLinija rez=new ListaLinija();
		try {
			PreparedStatement ps = c.prepareStatement("SELECT * FROM linija WHERE idMape=?");
			ps.setInt(1, Integer.parseInt(id));
			ResultSet rs=ps.executeQuery();
			while (rs.next()) {
				int idlinije=rs.getInt("idLinija");
				System.out.println(idlinije);
		    	String naziv=rs.getString("nazivLinije");
		    	int tip=rs.getInt("idTipSaobracaja");
		    	int idMape= rs.getInt("idMape");
		    	double cijena=rs.getDouble("cijenaVoznje");
		    	int prevoznik=rs.getInt("prevoznik");
		    	String ime= dajImePrevoznika(prevoznik);
		    	String tips=dajTipSaobracaja(tip);
		    	rez.getLinije().add(new uLinija(tip,naziv,idMape,cijena,prevoznik,ime,tips,idlinije));
		        }
			rs.close();
			ps.close();
			c.close();
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			if(c!=null) c.close();
			e.printStackTrace();
		}
		return rez;
		
	}
	public static ListaLinija dajLinije() throws SQLException
	{
		Connection c = daoKonekcija.dajKonekciju();
		ListaLinija rez=new ListaLinija();
		String iskaz = "SELECT * FROM linija";
		try {
			Statement stmt = c.createStatement();
			ResultSet rs=stmt.executeQuery(iskaz);
			
			
			while (rs.next()) {
		    	String naziv=rs.getString("nazivLinije");
		    	int tip=rs.getInt("idTipSaobracaja");
		    	int idlinije=rs.getInt("idLinija");
		    	int idMape= rs.getInt("idMape");
		    	double cijena=rs.getDouble("cijenaVoznje");
		    	int prevoznik=rs.getInt("prevoznik");
		    	String ime= dajImePrevoznika(prevoznik);
		    	String tips=dajTipSaobracaja(tip);
		        rez.getLinije().add(new uLinija(tip,naziv,idMape,cijena,prevoznik,ime,tips,idlinije)); 
		        }
		    
			rs.close();
			c.close();
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			if(c!=null)c.close();
			e.printStackTrace();
		}
		
		return rez;
		}
	
	static int dajidLinije(String naziv) throws SQLException
	{
		Connection c = daoKonekcija.dajKonekciju();
		PreparedStatement ps=c.prepareStatement("SELECT idLinija FROM linija WHERE nazivLinije=?");
		ps.setString(1, naziv);
		ResultSet rs = ps.executeQuery();
		if(!rs.next()) return -1;
		//rs.close();
		return rs.getInt(1);
		
	}
	
	public static void brisiLiniju(int id) throws Exception
	{
		Connection c = daoKonekcija.dajKonekciju();
		CallableStatement cStmt = c.prepareCall("{CALL deleteLinija(?)}"); 
		cStmt.setInt(1,id);
		cStmt.execute(); 
		System.out.println("Uspjesno"); 
		cStmt.close();
	}
	
	public static ListaLinija dajLinijeLike(String poredi) throws SQLException{
		Connection c = daoKonekcija.dajKonekciju();
		ListaLinija rez=new ListaLinija();
		String iskaz = "SELECT nazivLinije FROM linija where nazivLinije like ?";
		try {
			PreparedStatement pstmt = c.prepareStatement(iskaz);
			pstmt.setString(1,poredi);
			ResultSet rs = pstmt.executeQuery();
			while (rs.next()) {
				String linija=rs.getString(1);
			    rez.getLinije().add(new uLinija(linija)); 
			    }
			rs.close();
			pstmt.close();
			c.close();
			return rez;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			if(c!=null) c.close();
			return rez;
		}
	}
	
	public static ListaLinija dajSortiraneLinije() throws SQLException
	{
		Connection c = daoKonekcija.dajKonekciju();
		ListaLinija rez=new ListaLinija();
		String iskaz = "SELECT nazivLinije,cijenaVoznje FROM linija ORDER BY cijenaVoznje ASC";
		try {
			Statement stmt = c.createStatement();
			ResultSet rs=stmt.executeQuery(iskaz);
			
			
			while (rs.next()) {
		    	String naziv=rs.getString("nazivLinije");
		    	double cijena=rs.getDouble("cijenaVoznje");
		    	
		        rez.getLinije().add(new uLinija(naziv,cijena));
		        }
		    
			rs.close();
			c.close();
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return rez;
		}
}
